import os
import re
import pandas as pd
import tkinter as tk
from tkinter import filedialog, ttk, scrolledtext
import datetime
import json

class WastewaterDataConverter:
    def __init__(self, root):
        self.root = root
        self.root.title("稻草人污水厂数据转换工具 - Python版")
        self.root.geometry("800x700")
        self.root.configure(bg="#f0f8ff")
        
        self.excel_data = None
        self.extracted_data = None
        self.current_file = None
        self.current_sheet = None
        
        self.create_ui()
    
    def create_ui(self):
        # 主容器
        main_frame = tk.Frame(self.root, bg="#f0f8ff", padx=20, pady=20)
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 标题
        title_label = tk.Label(
            main_frame, 
            text="稻草人污水厂数据转换工具 - Python版", 
            font=("Arial", 18, "bold"), 
            bg="#f0f8ff", 
            fg="#1e90ff"
        )
        title_label.pack(pady=(0, 20))
        
        # 说明文本
        info_frame = tk.LabelFrame(main_frame, text="数据提取说明", bg="#f0f8ff", padx=10, pady=10)
        info_frame.pack(fill=tk.X, pady=(0, 20))
        
        info_text = """本工具将从Excel文件中提取以下污水厂数据列：
1. 监控时间（将统一转换为HH:00格式）
2. 化学需氧量（毫克/升）
3. 氨氮（毫克/升）
4. 总氮（毫克/升）
5. 总磷（毫克/升）

请上传包含上述数据的Excel文件。"""
        
        info_label = tk.Label(info_frame, text=info_text, justify=tk.LEFT, bg="#f0f8ff")
        info_label.pack(anchor=tk.W)
        
        # 文件上传区域
        upload_frame = tk.LabelFrame(main_frame, text="文件上传", bg="#f0f8ff", padx=10, pady=10)
        upload_frame.pack(fill=tk.X, pady=(0, 20))
        
        file_frame = tk.Frame(upload_frame, bg="#f0f8ff")
        file_frame.pack(fill=tk.X)
        
        self.file_label = tk.Label(file_frame, text="未选择文件", bg="#f0f8ff", width=40)
        self.file_label.pack(side=tk.LEFT, padx=(0, 10))
        
        browse_button = tk.Button(
            file_frame, 
            text="浏览...", 
            command=self.browse_file,
            bg="#1e90ff",
            fg="white",
            padx=10
        )
        browse_button.pack(side=tk.LEFT)
        
        # 工作表选择
        sheet_frame = tk.Frame(upload_frame, bg="#f0f8ff", pady=10)
        sheet_frame.pack(fill=tk.X)
        
        sheet_label = tk.Label(sheet_frame, text="工作表：", bg="#f0f8ff")
        sheet_label.pack(side=tk.LEFT, padx=(0, 10))
        
        self.sheet_combobox = ttk.Combobox(sheet_frame, state="disabled", width=30)
        self.sheet_combobox.pack(side=tk.LEFT)
        self.sheet_combobox.bind("<<ComboboxSelected>>", self.process_sheet)
        
        # 选项设置区域
        options_frame = tk.LabelFrame(main_frame, text="选项设置", bg="#f0f8ff", padx=10, pady=10)
        options_frame.pack(fill=tk.X, pady=(0, 20))
        
        # 数组名称
        name_frame = tk.Frame(options_frame, bg="#f0f8ff", pady=5)
        name_frame.pack(fill=tk.X)
        
        name_label = tk.Label(name_frame, text="数组名称：", bg="#f0f8ff")
        name_label.pack(side=tk.LEFT, padx=(0, 10))
        
        self.array_name = tk.Entry(name_frame, width=30)
        self.array_name.insert(0, "data")
        self.array_name.pack(side=tk.LEFT)
        
        # 数组格式
        format_frame = tk.Frame(options_frame, bg="#f0f8ff", pady=5)
        format_frame.pack(fill=tk.X)
        
        format_label = tk.Label(format_frame, text="数组格式：", bg="#f0f8ff")
        format_label.pack(side=tk.LEFT, padx=(0, 10))
        
        self.array_format = ttk.Combobox(format_frame, width=28)
        self.array_format.pack(side=tk.LEFT)
        self.array_format['values'] = ('python', 'javascript', 'php', 'java', 'csharp')
        self.array_format.current(0)
        self.array_format.bind("<<ComboboxSelected>>", self.update_result)
        
        # 结果区域
        result_frame = tk.LabelFrame(main_frame, text="转换结果", bg="#f0f8ff", padx=10, pady=10)
        result_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
        
        # 结果文本框
        self.result_text = scrolledtext.ScrolledText(
            result_frame, 
            wrap=tk.NONE, 
            width=80, 
            height=15,
            font=("Courier New", 10)
        )
        self.result_text.pack(fill=tk.BOTH, expand=True)
        self.result_text.insert(tk.END, "请上传Excel文件并选择相应选项后查看结果")
        
        # 按钮区域
        button_frame = tk.Frame(result_frame, bg="#f0f8ff", pady=10)
        button_frame.pack(fill=tk.X)
        
        copy_button = tk.Button(
            button_frame, 
            text="复制到剪贴板", 
            command=self.copy_to_clipboard,
            bg="#1e90ff",
            fg="white",
            padx=10
        )
        copy_button.pack(side=tk.LEFT, padx=(0, 10))
        
        download_button = tk.Button(
            button_frame, 
            text="下载为TXT文件", 
            command=self.download_as_txt,
            bg="#1e90ff",
            fg="white",
            padx=10
        )
        download_button.pack(side=tk.LEFT, padx=(0, 10))
        
        reset_button = tk.Button(
            button_frame, 
            text="页面重置", 
            command=self.reset_page,
            bg="#ff6347",
            fg="white",
            padx=10
        )
        reset_button.pack(side=tk.LEFT)
        
        # 状态标签
        self.status_label = tk.Label(result_frame, text="", bg="#f0f8ff", fg="#1e90ff")
        self.status_label.pack(pady=(5, 0))
    
    def browse_file(self):
        file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=[("Excel文件", "*.xlsx *.xls")]
        )
        
        if not file_path:
            return
        
        self.current_file = file_path
        self.file_label.config(text=os.path.basename(file_path))
        
        try:
            # 读取Excel文件的所有工作表
            excel_file = pd.ExcelFile(file_path)
            sheet_names = excel_file.sheet_names
            
            # 更新工作表下拉框
            self.sheet_combobox['values'] = sheet_names
            self.sheet_combobox.current(0)
            self.sheet_combobox['state'] = 'readonly'
            
            # 处理第一个工作表
            self.process_sheet(None)
            
        except Exception as e:
            self.show_error(f"读取文件时发生错误: {str(e)}")
    
    def process_sheet(self, event):
        if not self.current_file:
            return
        
        sheet_name = self.sheet_combobox.get()
        if not sheet_name:
            return
        
        self.current_sheet = sheet_name
        
        try:
            # 读取选定的工作表
            df = pd.read_excel(self.current_file, sheet_name=sheet_name, header=None)
            
            # 过滤空行
            df = df.dropna(how='all')
            
            # 将DataFrame转换为列表
            data = df.values.tolist()
            
            # 提取特定列
            self.extracted_data = self.extract_specific_columns(data)
            
            # 更新结果
            self.update_result(None)
            
        except Exception as e:
            self.show_error(f"处理工作表时发生错误: {str(e)}")
    
    def extract_specific_columns(self, data):
        if not data or len(data) == 0:
            return []
        
        # 处理复式表头 - 合并前几行的表头信息
        merged_headers = self.get_merged_headers(data)
        
        # 查找目标列的索引
        time_index = self.find_column_index(merged_headers, '监控时间')
        cod_index = self.find_column_index(merged_headers, '化学需氧量', '毫克/升')
        nh3n_index = self.find_column_index(merged_headers, '氨氮', '毫克/升')
        tn_index = self.find_column_index(merged_headers, '总氮', '毫克/升')
        tp_index = self.find_column_index(merged_headers, '总磷', '毫克/升')
        
        # 创建新的数据数组，包含列标题和数据
        extracted_data = []
        
        # 添加列标题
        new_headers = []
        if time_index != -1: new_headers.append('监控时间')
        if cod_index != -1: new_headers.append('化学需氧量(毫克/升)')
        if nh3n_index != -1: new_headers.append('氨氮(毫克/升)')
        if tn_index != -1: new_headers.append('总氮(毫克/升)')
        if tp_index != -1: new_headers.append('总磷(毫克/升)')
        
        extracted_data.append(new_headers)
        
        # 确定数据起始行（跳过所有表头行）
        data_start_row = self.get_data_start_row(data)
        
        # 添加数据行
        for i in range(data_start_row, len(data)):
            row = data[i]
            new_row = []
            
            if time_index != -1:
                # 处理监控时间，将时间部分统一为HH:00格式
                time_value = row[time_index] if time_index < len(row) else ''
                time_value = self.standardize_time_format(time_value)
                new_row.append(time_value)
            
            if cod_index != -1: 
                new_row.append(row[cod_index] if cod_index < len(row) else '')
            if nh3n_index != -1: 
                new_row.append(row[nh3n_index] if nh3n_index < len(row) else '')
            if tn_index != -1: 
                new_row.append(row[tn_index] if tn_index < len(row) else '')
            if tp_index != -1: 
                new_row.append(row[tp_index] if tp_index < len(row) else '')
            
            extracted_data.append(new_row)
        
        return extracted_data
    
    def standardize_time_format(self, time_value):
        if not time_value:
            return ''
        
        # 将time_value转换为字符串
        time_value = str(time_value)
        
        # 尝试识别常见的日期时间格式
        date_match = None
        
        # 匹配格式：YYYY-MM-DD HH:MM:SS 或 YYYY/MM/DD HH:MM:SS
        date_match = re.match(r'^(\d{4}[-/]\d{1,2}[-/]\d{1,2})\s+(\d{1,2}):\d{1,2}(:\d{1,2})?', time_value)
        if date_match:
            return f"{date_match.group(1)} {date_match.group(2)}:00"
        
        # 匹配格式：YYYY-MM-DD HH 或 YYYY/MM/DD HH（只有小时没有分钟）
        date_match = re.match(r'^(\d{4}[-/]\d{1,2}[-/]\d{1,2})\s+(\d{1,2})$', time_value)
        if date_match:
            return f"{date_match.group(1)} {date_match.group(2)}:00"
        
        # 匹配格式：MM-DD-YYYY HH:MM:SS 或 MM/DD/YYYY HH:MM:SS
        date_match = re.match(r'^(\d{1,2}[-/]\d{1,2}[-/]\d{4})\s+(\d{1,2}):\d{1,2}(:\d{1,2})?', time_value)
        if date_match:
            return f"{date_match.group(1)} {date_match.group(2)}:00"
        
        # 匹配格式：MM-DD-YYYY HH 或 MM/DD/YYYY HH（只有小时没有分钟）
        date_match = re.match(r'^(\d{1,2}[-/]\d{1,2}[-/]\d{4})\s+(\d{1,2})$', time_value)
        if date_match:
            return f"{date_match.group(1)} {date_match.group(2)}:00"
        
        # 尝试将pandas时间戳对象转换为日期时间字符串
        try:
            if isinstance(time_value, pd.Timestamp) or isinstance(time_value, datetime.datetime):
                dt = pd.to_datetime(time_value)
                return dt.strftime('%Y-%m-%d %H:00')
        except:
            pass
        
        # 如果没有匹配任何已知格式，返回原始值
        return time_value
    
    def get_merged_headers(self, data):
        # 检查数据是否足够
        if len(data) < 2:
            return data[0] if data else []
        
        # 假设前3行可能是表头（复式表头通常不会超过3行）
        max_header_rows = min(3, len(data) - 1)
        
        # 创建合并后的表头数组
        merged_headers = []
        
        # 获取所有列的最大数量
        max_columns = max([len(row) for row in data[:max_header_rows]])
        
        # 对每一列进行处理
        for col_index in range(max_columns):
            # 收集该列在所有可能的表头行中的值
            header_values = []
            
            for row_index in range(max_header_rows):
                row = data[row_index]
                if col_index < len(row) and row[col_index] is not None and str(row[col_index]).strip():
                    header_values.append(str(row[col_index]).strip())
            
            # 合并该列的所有表头值
            merged_headers.append(' '.join(header_values))
        
        return merged_headers
    
    def find_column_index(self, headers, primary_keyword, secondary_keyword=None):
        # 首先尝试精确匹配
        for i, header in enumerate(headers):
            header_lower = str(header).lower()
            
            # 检查是否包含所有关键词
            if primary_keyword.lower() in header_lower:
                if not secondary_keyword or secondary_keyword.lower() in header_lower:
                    return i
        
        # 如果没有找到精确匹配，尝试模糊匹配
        for i, header in enumerate(headers):
            header_lower = str(header).lower()
            
            # 检查是否包含主关键词
            if primary_keyword.lower() in header_lower:
                return i
        
        return -1  # 未找到匹配列
    
    def get_data_start_row(self, data):
        # 检查数据是否足够
        if len(data) < 2:
            return 1
        
        # 假设前3行可能是表头（复式表头通常不会超过3行）
        max_header_rows = min(3, len(data) - 1)
        
        # 检查每一行，看是否可能是表头
        for i in range(max_header_rows, min(max_header_rows + 3, len(data))):
            row = data[i]
            
            # 检查该行是否包含数值数据（通常数据行会有数值）
            has_numeric_data = any(
                isinstance(cell, (int, float)) or 
                (isinstance(cell, str) and cell.strip() and cell.strip().replace('.', '', 1).isdigit())
                for cell in row
            )
            
            if has_numeric_data:
                return i
        
        # 默认返回最大表头行数之后的行
        return max_header_rows
    
    def update_result(self, event):
        if not self.extracted_data:
            return
        
        format_type = self.array_format.get()
        array_name = self.array_name.get() or "data"
        
        # 根据选择的格式生成代码
        code = ""
        
        if format_type == "python":
            code = f"{array_name} = {json.dumps(self.extracted_data, ensure_ascii=False, indent=2)}"
        
        elif format_type == "javascript":
            code = f"const {array_name} = {json.dumps(self.extracted_data, ensure_ascii=False, indent=2)};"
        
        elif format_type == "php":
            # PHP数组格式转换
            php_array = "array("
            for row_index, row in enumerate(self.extracted_data):
                php_array += "\n  array("
                for cell_index, cell in enumerate(row):
                    # 处理不同类型的值
                    if isinstance(cell, str):
                        # value = f'"{cell.replace("\"", "\\\"")}"'
                        value = ""
                    elif cell is None:
                        value = "null"
                    else:
                        value = str(cell)
                    
                    php_array += f"{value}{', ' if cell_index < len(row) - 1 else ''}"
                
                php_array += f"){', ' if row_index < len(self.extracted_data) - 1 else ''}"
            
            php_array += "\n);"
            code = f"${array_name} = {php_array}"
        
        elif format_type == "java":
            # Java数组格式转换
            java_array = "new Object[][] {"
            for row_index, row in enumerate(self.extracted_data):
                java_array += "\n  {"
                for cell_index, cell in enumerate(row):
                    # 处理不同类型的值
                    if isinstance(cell, str):
                        value = ""
                        # value = f'"{cell.replace("\"", "\\\"")}"'
                    elif cell is None:
                        value = "null"
                    elif isinstance(cell, (int, float)):
                        value = str(cell)
                    else:
                        value = ""
                        # value = f'"{str(cell).replace("\"", "\\\"")}"'

                    java_array += f"{value}{', ' if cell_index < len(row) - 1 else ''}"
                
                java_array += f"}}{', ' if row_index < len(self.extracted_data) - 1 else ''}"
            
            java_array += "\n};"
            code = f"Object[][] {array_name} = {java_array}"
        
        elif format_type == "csharp":
            # C#数组格式转换
            csharp_array = "new object[,] {"
            row_count = len(self.extracted_data)
            col_count = max([len(row) for row in self.extracted_data])
            
            for row_index, row in enumerate(self.extracted_data):
                csharp_array += "\n  {"
                for i in range(col_count):
                    cell = row[i] if i < len(row) else None
                    
                    # 处理不同类型的值
                    if isinstance(cell, str):
                        value = ""
                        # value = f'"{cell.replace("\"", "\\\"")}"'
                    elif cell is None:
                        value = "null"
                    elif isinstance(cell, (int, float)):
                        value = str(cell)
                    else:
                        # value = f'"{str(cell).replace("\"", "\\\"")}"'
                        value = ""

                    csharp_array += f"{value}{', ' if i < col_count - 1 else ''}"
                
                csharp_array += f"}}{', ' if row_index < row_count - 1 else ''}"
            
            csharp_array += "\n};"
            code = f"object[,] {array_name} = {csharp_array}"
        
        # 更新结果文本框
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, code)
    
    def copy_to_clipboard(self):
        text = self.result_text.get(1.0, tk.END)
        self.root.clipboard_clear()
        self.root.clipboard_append(text)
        
        self.show_status("已复制到剪贴板！")
    
    def download_as_txt(self):
        text = self.result_text.get(1.0, tk.END)
        format_type = self.array_format.get()
        array_name = self.array_name.get() or "data"
        
        file_path = filedialog.asksaveasfilename(
            defaultextension=".txt",
            filetypes=[("文本文件", "*.txt")],
            initialfile=f"{array_name}_{format_type}.txt"
        )
        
        if file_path:
            with open(file_path, "w", encoding="utf-8") as f:
                f.write(text)
            
            self.show_status("文件已下载！")
    
    def reset_page(self):
        # 重置所有状态
        self.excel_data = None
        self.extracted_data = None
        self.current_file = None
        self.current_sheet = None
        
        self.file_label.config(text="未选择文件")
        self.array_name.delete(0, tk.END)
        self.array_name.insert(0, "data")
        self.array_format.current(0)
        
        self.sheet_combobox['values'] = []
        self.sheet_combobox.set("")
        self.sheet_combobox['state'] = "disabled"
        
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, "请上传Excel文件并选择相应选项后查看结果")
        
        self.show_status("页面已重置")
    
    def show_error(self, message):
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, f"错误：{message}")
        self.result_text.tag_configure("error", foreground="red")
        self.result_text.tag_add("error", 1.0, tk.END)
    
    def show_status(self, message):
        self.status_label.config(text=message)
        # 2秒后清除状态消息
        self.root.after(2000, lambda: self.status_label.config(text=""))

# 主程序入口
if __name__ == "__main__":
    root = tk.Tk()
    app = WastewaterDataConverter(root)
    root.mainloop()